package org.compiere.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.compiere.acct.Doc;
import org.compiere.apps.ADialog;
import org.compiere.apps.IProcessParameter;
import org.compiere.apps.ProcessCtl;
import org.compiere.apps.ProcessParameterPanel;
import org.compiere.model.MAcctSchema;
import org.compiere.model.MBPartner;
import org.compiere.model.MConversionType;
import org.compiere.model.MCurrency;
import org.compiere.model.MGLCategory;
import org.compiere.model.MJournal;
import org.compiere.model.MJournalBatch;
import org.compiere.model.MJournalLine;
import org.compiere.model.MPeriod;
import org.compiere.model.MSequence;
import org.compiere.model.X_C_ValidCombination;
import org.compiere.util.ASyncProcess;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class FI_IncomeExpendClose extends SvrProcess implements ASyncProcess {

	
	private int C_AcctSchema_ID;
	private int C_DocType_ID;
	
	private int C_Period_ID;
	private String Revenue;
	private String Expences;
	private String Revenue_Expence_Account;
	private int Revenue_Expence_AccountID = 0;
	
	String journalDescription = "Закрытие дебиторской/кредиторской задолженности";
	
	private Properties ctx;
	
	private CLogger Log = CLogger.getCLogger(IncomeExpedentureCloseMonth.class);
	
	MJournalBatch batch;
	MPeriod period;
	
	private boolean isHasJournals;
	private static int closeCounter = 0;
	private int journalCount = 1;
	
	@Override
	protected void prepare() {
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null); 
			else if (para[i].getParameterName().compareTo("C_Period_ID") == 0)
			{
				C_Period_ID = Integer.parseInt(para[i].getParameter().toString());
			}
			else if (para[i].getParameterName().compareTo("Revenue_Expence_Account") == 0)
			{
				Revenue_Expence_Account = para[i].getParameter().toString();
			}
			else if (para[i].getParameterName().compareTo("Revenue") == 0)
			{
				Revenue = para[i].getParameter().toString();
			}
			else if (para[i].getParameterName().compareTo("Expences") == 0)
			{
				Expences = para[i].getParameter().toString();
			}
			else if (para[i].getParameterName().compareTo("C_AcctSchema_ID") == 0)
			{
				C_AcctSchema_ID = Integer.parseInt(para[i].getParameter().toString());
			}
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}
			
			ctx = getCtx();
		}
	}

	@Override
	protected String doIt() throws Exception {
		C_DocType_ID = getC_DocType();

		String oldBatchsql = "SELECT GL_JournalBatch_ID FROM GL_JournalBatch WHERE C_DocType_ID = " + C_DocType_ID + 
								" AND C_Period_ID = " + C_Period_ID + " AND DocStatus IN ('" + DocAction.STATUS_Closed + "', '" + DocAction.STATUS_Completed + "')";
		
		String sqlCloseAcc = "SELECT C_ElementValue_ID from adempiere.C_ElementValue WHERE Value = '" + Revenue_Expence_Account + "'";
		Revenue_Expence_AccountID = DB.getSQLValue(get_TrxName(), sqlCloseAcc);
		if(Revenue_Expence_AccountID < 0) return "Error Revenue_Expence_AccountID";
		
		int oldBatch_ID = DB.getSQLValue(get_TrxName(), oldBatchsql);
		
		if(oldBatch_ID != -1 && closeCounter == 0)
		{
			if(ADialog.ask(25, null, "Процесс \"Закрытие месяца. Доходы/Расходы\" уже был успешно завершен. Вы хотите запустить данный процесс повторно?"))
			{
				ReClose(oldBatch_ID);
				close();
				return "Период закрыт";
			}
			else
			{
				return "Операция отменена";
			}
		}
		
		closeCounter = 0;
		
		period = new MPeriod(getCtx(), C_Period_ID, get_TrxName());
		
		batch = CreateBatch();
		
		String sql = getSql(period.getStartDate() , period.getEndDate(), Revenue);		
		ClosePeriod(sql, false);
		
		String sql2 = getSql(period.getStartDate() , period.getEndDate(), Expences);		
		ClosePeriod(sql2, true);
		
		String error = postBatch(batch);
		
		if(isHasJournals)
		{
			batch.setDocAction(DocAction.ACTION_Close);
			batch.setDocStatus(DocAction.STATUS_Closed);
			batch.saveEx();
		}
		else
			batch.delete(true);
		return error;
	}
	
	private String getSql(Timestamp dateFrom, Timestamp dateTo, String account) throws SQLException
	{
		StringBuffer sql = new StringBuffer();
		
		sql.append("select \n\t sum(amtacctcr) as Credit, \n\t sum(amtacctdr) as Debit, \n\t f.C_BPartner_ID,");
		sql.append("\n\t e.c_elementvalue_id \n from \n\t adempiere.fact_acct f  \n\t inner join adempiere.c_elementvalue e on f.account_id = e.c_elementvalue_id ");
		sql.append("\n where \n\t f.dateacct between '");
		sql.append(dateFrom).append("' and '").append(dateTo).append("'");
		sql.append("\n\t and (e.value like '").append(account).append("') \n group by ");
		sql.append("\n\t e.value, \n\t e.c_elementvalue_id, \n\t f.C_BPartner_ID \n order by \n\t e.value");
		
		return sql.toString();
	}
	
	private void ClosePeriod(String sql, boolean isSeven) throws SQLException
	{
		MJournal journal = CreateJournal();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int lineValue = 10;
		
		BigDecimal credit = new BigDecimal(0d);
		BigDecimal debit = new BigDecimal(0d);
		
		BigDecimal currentCredit = new BigDecimal(0d);
		BigDecimal currentDebit = new BigDecimal(0d);
		
		try {
			pstmt = DB.prepareStatement(sql, null);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {				
				if(isSeven) {
					currentCredit = rs.getBigDecimal("Debit").subtract(rs.getBigDecimal("Credit"));
					currentDebit = new BigDecimal(0d).abs();
					
					debit = debit.add(currentDebit);
					credit = debit.add(currentCredit);
					
					CreateLine(journal, currentDebit, currentCredit, Revenue_Expence_AccountID, rs.getInt("C_BPartner_ID"), lineValue);
					CreateLine(journal, currentCredit, currentDebit, rs.getInt("C_ElementValue_ID"), rs.getInt("C_BPartner_ID"), lineValue + 10);
				}
				else {
					currentCredit = new BigDecimal(0d).abs();
					currentDebit = rs.getBigDecimal("Credit").subtract(rs.getBigDecimal("Debit"));
					
					debit = debit.add(currentDebit);
					credit = debit.add(currentCredit);
					
					CreateLine(journal, currentDebit, currentCredit, Revenue_Expence_AccountID, rs.getInt("C_BPartner_ID"), lineValue);
					CreateLine(journal, currentCredit, currentDebit, rs.getInt("C_ElementValue_ID"), rs.getInt("C_BPartner_ID"), lineValue + 10);
				}


				lineValue += 20;
			}
		}
		catch(Exception ex) {
			log.severe("FI_IncomeExpendClose. ClosePeriod. Operation: CreateLine(). Error occured.\n" + ex.toString());
		}
		finally {
			rs.close(); pstmt.close();
			rs = null; pstmt = null;
		}
		
		journal.setTotalCr(credit);
		journal.setTotalDr(debit);
		journal.saveEx();
	}
	
	private MJournalBatch CreateBatch()
	{
		MJournalBatch batch = new MJournalBatch(ctx, 0, get_TrxName());
		batch.setDescription(journalDescription + period.getName());
		batch.setPostingType(MJournal.POSTINGTYPE_Actual);
		batch.setC_DocType_ID(C_DocType_ID);
		batch.setDateDoc(period.getEndDate());
		batch.setDateAcct(period.getEndDate());
		batch.setC_Period_ID(period.get_ID());
		batch.setGL_Category_ID(MGLCategory.getDefaultSystem(ctx).get_ID());
		batch.setC_Currency_ID(MCurrency.get(ctx, "KZT").get_ID());
		batch.save();
		
		return batch;
	}
	
	private MJournal CreateJournal()
	{
		isHasJournals = true;
		
		String dn = MSequence.getDocumentNo (Env.getAD_Client_ID(ctx), "GL_Journal", get_TrxName(), null);
		
		MJournal journal = new MJournal(ctx, 0, get_TrxName());
		journal.setClientOrg(getAD_Client_ID(), Env.getAD_Org_ID(ctx));
		journal.setGL_JournalBatch_ID(batch.get_ID());
		journal.setC_Period_ID(C_Period_ID);
		journal.setDateDoc(period.getEndDate());
		journal.setDateAcct(period.getEndDate());		
		journal.setDocumentNo(dn);
		journal.setDescription(journalDescription);		
		journal.setC_DocType_ID(C_DocType_ID);
		journal.setC_AcctSchema_ID(C_AcctSchema_ID);
		journal.setGL_Category_ID(batch.getGL_Category_ID());
		journal.setC_Currency_ID(batch.getC_Currency_ID());
		journal.setC_ConversionType_ID(MConversionType.getDefault(Env.getAD_Client_ID(ctx)));
		journal.setDocStatus(MJournalBatch.STATUS_InProgress);
		journal.setDocAction(MJournalBatch.ACTION_None);
		journal.setIsPrinted(false);
		journal.setPostingType(batch.getPostingType());
		journal.setIsActive(true);
		journal.setC_AcctSchema_ID(C_AcctSchema_ID);
		journal.setC_DocType_ID(batch.getC_DocType_ID());
		journal.setCurrencyRate(new BigDecimal(1));		
		journal.setProcessing(false);
		journal.setProcessed(false);
		journal.setPosted(true);
		journal.save();
		
		journalDescription = journalDescription.concat(String.valueOf(journalCount++));
		
		return journal;
	}
	
	private void CreateLine(MJournal journal, BigDecimal credit, BigDecimal debit, int account_ID, int C_BPartner_ID, int lineValue)
	{
		int C_ValidCombination_ID = getCombination(C_BPartner_ID, account_ID);
		MJournalLine line = new MJournalLine(ctx, 0, get_TrxName());
		line.setC_ValidCombination_ID(C_ValidCombination_ID);
		line.setAmtSourceCr(credit);
		line.setAmtSourceDr(debit);
		line.setLine(lineValue);
		line.setGL_Journal_ID(journal.get_ID());
		line.setC_Currency_ID(journal.getC_Currency_ID());
		line.setC_ConversionType_ID(journal.getC_ConversionType_ID());
		line.saveEx();
	}
	
	private void ReClose(int oldBatch_ID)
	{
		MJournalBatch batch = new MJournalBatch(ctx, oldBatch_ID, get_TrxName());
		MJournalBatch newBatch = MJournalBatch.copyFrom(ctx, batch.get_ID(), batch.getDateAcct(), get_TrxName());
		
		MJournal[] journals = newBatch.getJournals(false);
		MJournal[] oldJournals = batch.getJournals(false);
		
		for(int i = 0; i < journals.length; i++) {
			MJournalLine[] lines = journals[i].getLines(false);
			
			for(MJournalLine line : lines)
			{
				line.setAmtSourceCr(line.getAmtSourceCr().negate());
				line.setAmtSourceDr(line.getAmtSourceDr().negate());
								
				if(!line.save())
					Log.log(Level.SEVERE, "MJournalLine reclose is not saved");
			}
			
			journals[i].setTotalCr(journals[i].getTotalCr().negate());
			journals[i].setTotalDr(journals[i].getTotalDr().negate());
			journals[i].setDocStatus(MJournalBatch.STATUS_InProgress);
			journals[i].setDocAction(MJournalBatch.ACTION_None);
			journals[i].setProcessing(false);
			journals[i].setProcessed(false);
			journals[i].setProcessed(false);
			journals[i].setPosted(true);			
			journals[i].saveEx();
			
			oldJournals[i].setPosted(true);
			oldJournals[i].setDocStatus(MJournal.ACTION_Void);
			oldJournals[i].setDocAction(MJournal.ACTION_Reject);
			if(!oldJournals[i].save())
				Log.log(Level.SEVERE, "MJournalLine reclose is not saved");
		}
		
		reActivate(newBatch);
		newBatch.voidIt();
		newBatch.setDocStatus(MJournal.ACTION_ReActivate);
		newBatch.setDocAction(DocAction.ACTION_Reject);
		if(!newBatch.save())
			Log.log(Level.SEVERE, "MJournalBatch reclose is not saved");
		batch.setDocStatus(MJournal.ACTION_Void);
		batch.setDocAction(MJournal.ACTION_Reject);
		batch.setProcessed(true);
		if(!batch.save())
			Log.log(Level.SEVERE, "MJournalBatch reclose is not saved");
	}
	private int getCombination(int C_BPartner_ID, int account)
	{
		String sql = "SELECT MAX(C_ValidCombination_ID) FROM C_ValidCombination WHERE C_BPartner_ID = " + C_BPartner_ID + " AND Account_ID = " + account;
		
		int C_ValidCombination_ID = DB.getSQLValue(get_TrxName(), sql);
		
		if(C_ValidCombination_ID <= 0)
		{
			X_C_ValidCombination combination = new X_C_ValidCombination(ctx, 0, get_TrxName());
			combination.setAccount_ID(account);
			combination.setC_AcctSchema_ID(C_AcctSchema_ID);
			combination.setC_BPartner_ID(C_BPartner_ID);
			MBPartner bpartner = new MBPartner(ctx, C_BPartner_ID, get_TrxName());			
			combination.setCombination(bpartner.getName() + "_" + account);
			
			combination.saveEx();
			
			C_ValidCombination_ID = combination.get_ID();
		}
		return C_ValidCombination_ID;
	}

	private int getC_DocType()
	{
		String sql = "SELECT C_DocType_ID FROM C_DocType WHERE Name = 'Closing of income and expenditure'";
		
		int C_DocType_ID = DB.getSQLValue(get_TrxName(), sql);
		
		return C_DocType_ID;
	}
	
	private MAcctSchema[] getAcctSchema(MJournal journal) {
		MAcctSchema as = MAcctSchema.get(Env.getCtx(), journal.getC_AcctSchema_ID(), journal.get_TrxName());
		MAcctSchema[] ass = new MAcctSchema[1];
		ass[0] = as;
		return ass;
	}

	/**
	 * @return
	 */
	private String postBatch(MJournalBatch batch) {
		if (batch == null) {
			return "Error";
		}
		batch.setProcessed(true);
		batch.saveEx(get_TrxName());
		batch.processIt(MJournalBatch.DOCACTION_Complete);
		String error = "";
		for(MJournal journal: batch.getJournals(false)) {
			MAcctSchema[] ass = getAcctSchema(journal);
			journal.processIt(DocAction.ACTION_Post);
			error = Doc.postImmediate(ass, MJournal.Table_ID, journal.getGL_Journal_ID(), true, journal.get_TrxName());
			log.log(Level.SEVERE,error);
		}
		return "OK";
	}
	
	/**
	 * @return
	 */
	private String reActivate(MJournalBatch batch) {
		if (batch == null) {
			return "Error";
		}
		batch.setProcessed(true);
		batch.saveEx(get_TrxName());
		batch.processIt(MJournalBatch.DOCACTION_Complete);
		String error = "";
		for(MJournal journal: batch.getJournals(false)) {
			MAcctSchema[] ass = getAcctSchema(journal);
			journal.processIt(DocAction.ACTION_Post);
			error = Doc.postImmediate(ass, MJournal.Table_ID, journal.getGL_Journal_ID(), true, journal.get_TrxName());
			
			journal.setDocStatus(DocAction.ACTION_ReActivate);
			journal.setDocAction(DocAction.ACTION_Reject);
			journal.saveEx();
			log.log(Level.SEVERE,error);
		}
		return "OK";
	}
	
	private void close() {
	//  Prepare Process
		if(closeCounter == 0) {
			ProcessInfo pi = new ProcessInfo ("The printing process of the order", 1000025);
			pi.setAD_User_ID (Env.getAD_User_ID(Env.getCtx()));
			pi.setAD_Client_ID(Env.getAD_Client_ID(Env.getCtx()));

			List<ProcessInfoParameter> po = new ArrayList<ProcessInfoParameter>();
			po.add(new ProcessInfoParameter("Revenue",Revenue,null,"",""));
			po.add(new ProcessInfoParameter("Expences",Expences,null,"",""));
			po.add(new ProcessInfoParameter("Revenue_Expence_Account",Revenue_Expence_Account,null,"",""));
			po.add(new ProcessInfoParameter("C_Period_ID",C_Period_ID,null,"",""));
			po.add(new ProcessInfoParameter("C_AcctSchema_ID",C_AcctSchema_ID,null,"",""));
			//
			ProcessInfoParameter[] pp = new ProcessInfoParameter[po.size()];
			po.toArray(pp);
			pi.setParameter(pp);
			//	Execute Process
			ProcessParameterPanel pu = new ProcessParameterPanel(0, pi);
			closeCounter++;
			ProcessCtl.process(this, 0, (IProcessParameter) pu, pi, null);
		}
	}
	
	@Override
	public boolean isUILocked() {
		return false;
	}

	@Override
	public void executeASync(ProcessInfo pi) {
	}
	
	@Override
	public void lockUI(ProcessInfo pi) {
	}

	@Override
	public void unlockUI(ProcessInfo pi) {
	}
}
